{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Session Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from snowflake.snowpark import Session\n",
    "from snowflake.snowpark.functions import col\n",
    "import configparser\n",
    "\n",
    "# In this case I'm loading my credentials as a dictionary\n",
    "snowflake_credentials_file = '../snowflake_creds.config'\n",
    "config = configparser.ConfigParser()\n",
    "config.read(snowflake_credentials_file)\n",
    "connection_parameters = dict(config['default'])\n",
    "session = Session.builder.configs(connection_parameters).create()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Exploration\n",
    "\n",
    "* Section covers common data exploration steps through Snowpark API\n",
    "* Code snippets involves reading in a Snowflake table, showcasing different filtering and subsetting options"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.use_database(\"SNOWPARK_DEFINITIVE_GUIDE\")\n",
    "session.use_schema(\"MY_SCHEMA\")\n",
    "\n",
    "purchase_history = session.table(\"PURCHASE_HISTORY\")\n",
    "campaign_info = session.table(\"CAMPAIGN_INFO\")\n",
    "complain_info = session.table(\"COMPLAINT_INFO\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"ID\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\"INCOME\"  |\"KIDHOME\"  |\"TEENHOME\"  |\"DT_CUSTOMER\"  |\"RECENCY\"  |\"MNTWINES\"  |\"MNTFRUITS\"  |\"MNTMEATPRODUCTS\"  |\"MNTFISHPRODUCTS\"  |\"MNTSWEETPRODUCTS\"  |\"MNTGOLDPRODS\"  |\"NUMDEALSPURCHASES\"  |\"NUMWEBPURCHASES\"  |\"NUMCATALOGPURCHASES\"  |\"NUMSTOREPURCHASES\"  |\"NUMWEBVISITSMONTH\"  |\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|5524  |1957          |Graduation   |Single            |58138     |0          |0           |2012-09-04     |58         |635         |88           |546                |172                |88                  |88              |3                    |8                  |10                     |4                    |7                    |\n",
      "|2174  |1954          |Graduation   |Single            |46344     |1          |1           |2014-03-08     |38         |11          |1            |6                  |2                  |1                   |6               |2                    |1                  |1                      |2                    |5                    |\n",
      "|4141  |1965          |Graduation   |Together          |71613     |0          |0           |2013-08-21     |26         |426         |49           |127                |111                |21                  |42              |1                    |8                  |2                      |10                   |4                    |\n",
      "|6182  |1984          |Graduation   |Together          |26646     |1          |0           |2014-02-10     |26         |11          |4            |20                 |10                 |3                   |5               |2                    |2                  |0                      |4                    |6                    |\n",
      "|5324  |1981          |PhD          |Married           |58293     |1          |0           |2014-01-19     |94         |173         |43           |118                |46                 |27                  |15              |5                    |5                  |3                      |6                    |5                    |\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "purchase_history.show(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "frist row: \n",
      "  Row(ID=5524, YEAR_BIRTH=1957, EDUCATION='Graduation', MARITAL_STATUS='Single', INCOME=58138, KIDHOME=0, TEENHOME=0, DT_CUSTOMER=datetime.date(2012, 9, 4), RECENCY=58, MNTWINES=635, MNTFRUITS=88, MNTMEATPRODUCTS=546, MNTFISHPRODUCTS=172, MNTSWEETPRODUCTS=88, MNTGOLDPRODS=88, NUMDEALSPURCHASES=3, NUMWEBPURCHASES=8, NUMCATALOGPURCHASES=10, NUMSTOREPURCHASES=4, NUMWEBVISITSMONTH=7)\n"
     ]
    }
   ],
   "source": [
    "all_rows = purchase_history.collect()\n",
    "print(\"frist row: \\n \", all_rows[0])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Count of Rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchase_history.count()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Displaying Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ID',\n",
       " 'YEAR_BIRTH',\n",
       " 'EDUCATION',\n",
       " 'MARITAL_STATUS',\n",
       " 'INCOME',\n",
       " 'KIDHOME',\n",
       " 'TEENHOME',\n",
       " 'DT_CUSTOMER',\n",
       " 'RECENCY',\n",
       " 'MNTWINES',\n",
       " 'MNTFRUITS',\n",
       " 'MNTMEATPRODUCTS',\n",
       " 'MNTFISHPRODUCTS',\n",
       " 'MNTSWEETPRODUCTS',\n",
       " 'MNTGOLDPRODS',\n",
       " 'NUMDEALSPURCHASES',\n",
       " 'NUMWEBPURCHASES',\n",
       " 'NUMCATALOGPURCHASES',\n",
       " 'NUMSTOREPURCHASES',\n",
       " 'NUMWEBVISITSMONTH']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchase_history.columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Filtering -  Row Based Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------------------------------------\n",
      "|\"ID\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\n",
      "-------------------------------------\n",
      "|1     |1961          |Graduation   |\n",
      "-------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from snowflake.snowpark.functions import col\n",
    "\n",
    "purchase_history.filter(col(\"id\") == 1)\\\n",
    ".select(col(\"ID\"), col(\"YEAR_BIRTH\"), col(\"EDUCATION\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------------------------------------------\n",
      "|\"ID\"   |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\n",
      "---------------------------------------------------------\n",
      "|5324   |1981          |PhD          |Married           |\n",
      "|6177   |1985          |PhD          |Married           |\n",
      "|1994   |1983          |Graduation   |Married           |\n",
      "|9736   |1980          |Graduation   |Married           |\n",
      "|5376   |1979          |Graduation   |Married           |\n",
      "|2404   |1976          |Graduation   |Married           |\n",
      "|9422   |1989          |Graduation   |Married           |\n",
      "|10755  |1976          |2n Cycle     |Married           |\n",
      "|503    |1985          |Master       |Married           |\n",
      "|2139   |1975          |Master       |Married           |\n",
      "---------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "purchase_history.filter((col(\"MARITAL_STATUS\") == \"Married\") & (col(\"KIDHOME\") == 1))\\\n",
    ".select(col(\"ID\"), col(\"YEAR_BIRTH\"), col(\"EDUCATION\"), col(\"MARITAL_STATUS\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------------\n",
      "|\"ID\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\n",
      "--------------------------------------------------------\n",
      "|4141  |1965          |Graduation   |Together          |\n",
      "|7446  |1967          |Master       |Together          |\n",
      "|965   |1971          |Graduation   |Divorced          |\n",
      "|4855  |1974          |PhD          |Together          |\n",
      "|387   |1976          |Basic        |Married           |\n",
      "|9736  |1980          |Graduation   |Married           |\n",
      "|5376  |1979          |Graduation   |Married           |\n",
      "|7892  |1969          |Graduation   |Single            |\n",
      "|2404  |1976          |Graduation   |Married           |\n",
      "|1966  |1965          |PhD          |Married           |\n",
      "--------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "purchase_history.filter((col(\"YEAR_BIRTH\") >= 1964) & (col(\"YEAR_BIRTH\") <= 1980))\\\n",
    ".select(col(\"ID\"), col(\"YEAR_BIRTH\"), col(\"EDUCATION\"), col(\"MARITAL_STATUS\")).show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Select -  Subsetting Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------------------------------------\n",
      "|\"ID\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\n",
      "-------------------------------------\n",
      "|5524  |1957          |Graduation   |\n",
      "|2174  |1954          |Graduation   |\n",
      "|4141  |1965          |Graduation   |\n",
      "|6182  |1984          |Graduation   |\n",
      "|5324  |1981          |PhD          |\n",
      "|7446  |1967          |Master       |\n",
      "|965   |1971          |Graduation   |\n",
      "|6177  |1985          |PhD          |\n",
      "|4855  |1974          |PhD          |\n",
      "|5899  |1950          |PhD          |\n",
      "-------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "purchase_history.select(col(\"ID\"), col(\"YEAR_BIRTH\"), col(\"EDUCATION\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------------------------------------\n",
      "|\"ID\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\n",
      "-------------------------------------\n",
      "|5524  |1957          |Graduation   |\n",
      "|2174  |1954          |Graduation   |\n",
      "|4141  |1965          |Graduation   |\n",
      "|6182  |1984          |Graduation   |\n",
      "|5324  |1981          |PhD          |\n",
      "|7446  |1967          |Master       |\n",
      "|965   |1971          |Graduation   |\n",
      "|6177  |1985          |PhD          |\n",
      "|4855  |1974          |PhD          |\n",
      "|5899  |1950          |PhD          |\n",
      "-------------------------------------\n",
      "\n",
      "-------------------------------------\n",
      "|\"ID\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\n",
      "-------------------------------------\n",
      "|5524  |1957          |Graduation   |\n",
      "|2174  |1954          |Graduation   |\n",
      "|4141  |1965          |Graduation   |\n",
      "|6182  |1984          |Graduation   |\n",
      "|5324  |1981          |PhD          |\n",
      "|7446  |1967          |Master       |\n",
      "|965   |1971          |Graduation   |\n",
      "|6177  |1985          |PhD          |\n",
      "|4855  |1974          |PhD          |\n",
      "|5899  |1950          |PhD          |\n",
      "-------------------------------------\n",
      "\n",
      "-------------------------------------\n",
      "|\"ID\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\n",
      "-------------------------------------\n",
      "|5524  |1957          |Graduation   |\n",
      "|2174  |1954          |Graduation   |\n",
      "|4141  |1965          |Graduation   |\n",
      "|6182  |1984          |Graduation   |\n",
      "|5324  |1981          |PhD          |\n",
      "|7446  |1967          |Master       |\n",
      "|965   |1971          |Graduation   |\n",
      "|6177  |1985          |PhD          |\n",
      "|4855  |1974          |PhD          |\n",
      "|5899  |1950          |PhD          |\n",
      "-------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#### Columns Can Also Referenced as Follows\n",
    "purchase_history.select(purchase_history.ID, purchase_history.Year_BIRTH, \n",
    "                        purchase_history.EDUCATION).show() # Only if column names contains no space\n",
    "\n",
    "purchase_history.select(purchase_history[\"ID\"], purchase_history[\"Year_BIRTH\"], \n",
    "                        purchase_history[\"EDUCATION\"]).show()\n",
    "\n",
    "purchase_history.select(\"ID\",\"YEAR_BIRTH\",\"EDUCATION\").show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Transformation\n",
    "\n",
    "* Section explains various data transformation steps thorughs joins and unions\n",
    "* Code snippets delineates use of joins and union with focus on usage of required suffix\n",
    "* Commented code blocks provide additional information on required column selection methodlogy"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"ID_LEFT\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\"INCOME\"  |\"KIDHOME\"  |\"TEENHOME\"  |\"DT_CUSTOMER\"  |\"RECENCY\"  |\"MNTWINES\"  |\"MNTFRUITS\"  |\"MNTMEATPRODUCTS\"  |\"MNTFISHPRODUCTS\"  |\"MNTSWEETPRODUCTS\"  |\"MNTGOLDPRODS\"  |\"NUMDEALSPURCHASES\"  |\"NUMWEBPURCHASES\"  |\"NUMCATALOGPURCHASES\"  |\"NUMSTOREPURCHASES\"  |\"NUMWEBVISITSMONTH\"  |\"ACCEPTEDCMP1\"  |\"ACCEPTEDCMP2\"  |\"ACCEPTEDCMP3\"  |\"ACCEPTEDCMP4\"  |\"ACCEPTEDCMP5\"  |\"RESPONSE\"  |\n",
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|5524       |1957          |Graduation   |Single            |58138     |0          |0           |2012-09-04     |58         |635         |88           |546                |172                |88                  |88              |3                    |8                  |10                     |4                    |7                    |0               |0               |0               |0               |0               |1           |\n",
      "|2174       |1954          |Graduation   |Single            |46344     |1          |1           |2014-03-08     |38         |11          |1            |6                  |2                  |1                   |6               |2                    |1                  |1                      |2                    |5                    |0               |0               |0               |0               |0               |0           |\n",
      "|4141       |1965          |Graduation   |Together          |71613     |0          |0           |2013-08-21     |26         |426         |49           |127                |111                |21                  |42              |1                    |8                  |2                      |10                   |4                    |0               |0               |0               |0               |0               |0           |\n",
      "|6182       |1984          |Graduation   |Together          |26646     |1          |0           |2014-02-10     |26         |11          |4            |20                 |10                 |3                   |5               |2                    |2                  |0                      |4                    |6                    |0               |0               |0               |0               |0               |0           |\n",
      "|5324       |1981          |PhD          |Married           |58293     |1          |0           |2014-01-19     |94         |173         |43           |118                |46                 |27                  |15              |5                    |5                  |3                      |6                    |5                    |0               |0               |0               |0               |0               |0           |\n",
      "|7446       |1967          |Master       |Together          |62513     |0          |1           |2013-09-09     |16         |520         |42           |98                 |0                  |42                  |14              |2                    |6                  |4                      |10                   |6                    |0               |0               |0               |0               |0               |0           |\n",
      "|965        |1971          |Graduation   |Divorced          |55635     |0          |1           |2012-11-13     |34         |235         |65           |164                |50                 |49                  |27              |4                    |7                  |3                      |7                    |6                    |0               |0               |0               |0               |0               |0           |\n",
      "|6177       |1985          |PhD          |Married           |33454     |1          |0           |2013-05-08     |32         |76          |10           |56                 |3                  |1                   |23              |2                    |4                  |0                      |4                    |8                    |0               |0               |0               |0               |0               |0           |\n",
      "|4855       |1974          |PhD          |Together          |30351     |1          |0           |2013-06-06     |19         |14          |0            |24                 |3                  |3                   |2               |1                    |3                  |0                      |2                    |9                    |0               |0               |0               |0               |0               |1           |\n",
      "|5899       |1950          |PhD          |Together          |5648      |1          |1           |2014-03-13     |68         |28          |0            |6                  |1                  |1                   |13              |1                    |1                  |0                      |0                    |20                   |0               |0               |1               |0               |0               |0           |\n",
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "purchase_campaign = purchase_history.join(campaign_info, purchase_history.ID == campaign_info.ID\\\n",
    "                            ,lsuffix=\"_left\", rsuffix=\"_right\")\n",
    "\n",
    "purchase_campaign = purchase_campaign.drop(\"ID_RIGHT\")\n",
    "purchase_campaign.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\"INCOME\"  |\"KIDHOME\"  |\"TEENHOME\"  |\"DT_CUSTOMER\"  |\"RECENCY\"  |\"MNTWINES\"  |\"MNTFRUITS\"  |\"MNTMEATPRODUCTS\"  |\"MNTFISHPRODUCTS\"  |\"MNTSWEETPRODUCTS\"  |\"MNTGOLDPRODS\"  |\"NUMDEALSPURCHASES\"  |\"NUMWEBPURCHASES\"  |\"NUMCATALOGPURCHASES\"  |\"NUMSTOREPURCHASES\"  |\"NUMWEBVISITSMONTH\"  |\"ACCEPTEDCMP1\"  |\"ACCEPTEDCMP2\"  |\"ACCEPTEDCMP3\"  |\"ACCEPTEDCMP4\"  |\"ACCEPTEDCMP5\"  |\"RESPONSE\"  |\"ID\"  |\"COMPLAIN\"  |\"Z_COSTCONTACT\"  |\"Z_REVENUE\"  |\n",
      "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|1957          |Graduation   |Single            |58138     |0          |0           |2012-09-04     |58         |635         |88           |546                |172                |88                  |88              |3                    |8                  |10                     |4                    |7                    |0               |0               |0               |0               |0               |1           |5524  |0           |3                |11           |\n",
      "|1954          |Graduation   |Single            |46344     |1          |1           |2014-03-08     |38         |11          |1            |6                  |2                  |1                   |6               |2                    |1                  |1                      |2                    |5                    |0               |0               |0               |0               |0               |0           |2174  |0           |3                |11           |\n",
      "|1965          |Graduation   |Together          |71613     |0          |0           |2013-08-21     |26         |426         |49           |127                |111                |21                  |42              |1                    |8                  |2                      |10                   |4                    |0               |0               |0               |0               |0               |0           |4141  |0           |3                |11           |\n",
      "|1984          |Graduation   |Together          |26646     |1          |0           |2014-02-10     |26         |11          |4            |20                 |10                 |3                   |5               |2                    |2                  |0                      |4                    |6                    |0               |0               |0               |0               |0               |0           |6182  |0           |3                |11           |\n",
      "|1981          |PhD          |Married           |58293     |1          |0           |2014-01-19     |94         |173         |43           |118                |46                 |27                  |15              |5                    |5                  |3                      |6                    |5                    |0               |0               |0               |0               |0               |0           |5324  |0           |3                |11           |\n",
      "|1967          |Master       |Together          |62513     |0          |1           |2013-09-09     |16         |520         |42           |98                 |0                  |42                  |14              |2                    |6                  |4                      |10                   |6                    |0               |0               |0               |0               |0               |0           |7446  |0           |3                |11           |\n",
      "|1971          |Graduation   |Divorced          |55635     |0          |1           |2012-11-13     |34         |235         |65           |164                |50                 |49                  |27              |4                    |7                  |3                      |7                    |6                    |0               |0               |0               |0               |0               |0           |965   |0           |3                |11           |\n",
      "|1985          |PhD          |Married           |33454     |1          |0           |2013-05-08     |32         |76          |10           |56                 |3                  |1                   |23              |2                    |4                  |0                      |4                    |8                    |0               |0               |0               |0               |0               |0           |6177  |0           |3                |11           |\n",
      "|1974          |PhD          |Together          |30351     |1          |0           |2013-06-06     |19         |14          |0            |24                 |3                  |3                   |2               |1                    |3                  |0                      |2                    |9                    |0               |0               |0               |0               |0               |1           |4855  |0           |3                |11           |\n",
      "|1950          |PhD          |Together          |5648      |1          |1           |2014-03-13     |68         |28          |0            |6                  |1                  |1                   |13              |1                    |1                  |0                      |0                    |20                   |0               |0               |1               |0               |0               |0           |5899  |0           |3                |11           |\n",
      "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "final_combined = purchase_campaign.join(complain_info, purchase_campaign[\"ID_LEFT\"] == complain_info.ID)\n",
    "final_combined = final_combined.drop(\"ID_LEFT\")\n",
    "final_combined.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_combined.write.save_as_table(\"MARKETING_DATA\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Join Column Name - Same on Right & Left Table\n",
    "# campaign_info = session.table(\"CAMPAIGN_INFO\")\n",
    "# purchase_history.join(campaign_info, \"ID\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Avoiding Random Suffix\n",
    "# campaign_info = session.table(\"CAMPAIGN_INFO\")\n",
    "# purchase_history.join(campaign_info, purchase_history.ID == campaign_info.ID,lsuffix=\"_left\", rsuffix=\"_right\").show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Selecting Required Columns\n",
    "\n",
    "# campaign_info = session.table(\"CAMPAIGN_INFO\")\n",
    "# purchase_history.join(campaign_info, purchase_history.ID == campaign_info.ID,\n",
    "#         lsuffix=\"_left\", rsuffix=\"_right\")\\\n",
    "#         .select(\"ID_LEFT\",\"YEAR_BIRTH\",\"EDUCATION\",\"ACCEPTEDCMP3\",\"ACCEPTEDCMP4\")\\\n",
    "#         .show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### UNION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"ID\"   |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\"INCOME\"  |\"KIDHOME\"  |\"TEENHOME\"  |\"DT_CUSTOMER\"  |\"RECENCY\"  |\"MNTWINES\"  |\"MNTFRUITS\"  |\"MNTMEATPRODUCTS\"  |\"MNTFISHPRODUCTS\"  |\"MNTSWEETPRODUCTS\"  |\"MNTGOLDPRODS\"  |\"NUMDEALSPURCHASES\"  |\"NUMWEBPURCHASES\"  |\"NUMCATALOGPURCHASES\"  |\"NUMSTOREPURCHASES\"  |\"NUMWEBVISITSMONTH\"  |\"ACCEPTEDCMP3\"  |\"ACCEPTEDCMP4\"  |\"ACCEPTEDCMP5\"  |\"ACCEPTEDCMP1\"  |\"ACCEPTEDCMP2\"  |\"COMPLAIN\"  |\"Z_COSTCONTACT\"  |\"Z_REVENUE\"  |\"RESPONSE\"  |\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|4860   |1970          |Graduation   |Single            |24206     |1          |0           |2013-03-08     |66         |7           |2            |8                  |3                  |2                   |3               |1                    |1                  |0                      |3                    |6                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|10757  |1967          |PhD          |Divorced          |28420     |1          |0           |2013-12-24     |36         |4           |2            |5                  |2                  |0                   |0               |1                    |1                  |0                      |2                    |6                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|4023   |1970          |Graduation   |Married           |22979     |1          |0           |2012-09-06     |29         |16          |17           |19                 |20                 |21                  |22              |3                    |3                  |2                      |2                    |8                    |0               |0               |0               |0               |0               |0           |3                |11           |1           |\n",
      "|6679   |1966          |Graduation   |Single            |33279     |0          |0           |2014-06-12     |29         |10          |3            |3                  |0                  |0                   |0               |1                    |0                  |0                      |3                    |3                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|9923   |1972          |Master       |Single            |46423     |1          |1           |2013-09-18     |6          |68          |0            |16                 |0                  |0                   |8               |3                    |2                  |0                      |4                    |7                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|7181   |1977          |Graduation   |Married           |30368     |0          |1           |2013-11-07     |97         |35          |0            |13                 |2                  |0                   |2               |2                    |2                  |0                      |3                    |8                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|3599   |1970          |Graduation   |Married           |63684     |0          |1           |2012-11-26     |61         |575         |80           |428                |208                |93                  |80              |3                    |8                  |4                      |8                    |5                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|405    |1964          |Graduation   |Divorced          |41638     |0          |1           |2013-02-13     |68         |315         |0            |31                 |4                  |0                   |91              |4                    |5                  |5                      |3                    |8                    |1               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|6870   |1959          |PhD          |Divorced          |68805     |0          |1           |2013-09-17     |73         |182         |2            |49                 |17                 |13                  |20              |1                    |3                  |1                      |7                    |2                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|832    |1966          |PhD          |Together          |65814     |0          |1           |2014-03-02     |90         |561         |14           |113                |10                 |14                  |35              |3                    |8                  |2                      |10                   |5                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "marketing_additional = session.table(\"MARKETING_ADDITIONAL\")\n",
    "marketing_additional.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No of rows in MARKETING_ADDITIONAL table:  240\n",
      "No of rows in PURCHASE_HISTORY table:  2000\n",
      "No of rows in UPDATED table:  2240\n",
      "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\"INCOME\"  |\"KIDHOME\"  |\"TEENHOME\"  |\"DT_CUSTOMER\"  |\"RECENCY\"  |\"MNTWINES\"  |\"MNTFRUITS\"  |\"MNTMEATPRODUCTS\"  |\"MNTFISHPRODUCTS\"  |\"MNTSWEETPRODUCTS\"  |\"MNTGOLDPRODS\"  |\"NUMDEALSPURCHASES\"  |\"NUMWEBPURCHASES\"  |\"NUMCATALOGPURCHASES\"  |\"NUMSTOREPURCHASES\"  |\"NUMWEBVISITSMONTH\"  |\"ACCEPTEDCMP1\"  |\"ACCEPTEDCMP2\"  |\"ACCEPTEDCMP3\"  |\"ACCEPTEDCMP4\"  |\"ACCEPTEDCMP5\"  |\"RESPONSE\"  |\"ID\"  |\"COMPLAIN\"  |\"Z_COSTCONTACT\"  |\"Z_REVENUE\"  |\n",
      "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|1957          |Graduation   |Single            |58138     |0          |0           |2012-09-04     |58         |635         |88           |546                |172                |88                  |88              |3                    |8                  |10                     |4                    |7                    |0               |0               |0               |0               |0               |1           |5524  |0           |3                |11           |\n",
      "|1954          |Graduation   |Single            |46344     |1          |1           |2014-03-08     |38         |11          |1            |6                  |2                  |1                   |6               |2                    |1                  |1                      |2                    |5                    |0               |0               |0               |0               |0               |0           |2174  |0           |3                |11           |\n",
      "|1965          |Graduation   |Together          |71613     |0          |0           |2013-08-21     |26         |426         |49           |127                |111                |21                  |42              |1                    |8                  |2                      |10                   |4                    |0               |0               |0               |0               |0               |0           |4141  |0           |3                |11           |\n",
      "|1984          |Graduation   |Together          |26646     |1          |0           |2014-02-10     |26         |11          |4            |20                 |10                 |3                   |5               |2                    |2                  |0                      |4                    |6                    |0               |0               |0               |0               |0               |0           |6182  |0           |3                |11           |\n",
      "|1981          |PhD          |Married           |58293     |1          |0           |2014-01-19     |94         |173         |43           |118                |46                 |27                  |15              |5                    |5                  |3                      |6                    |5                    |0               |0               |0               |0               |0               |0           |5324  |0           |3                |11           |\n",
      "|1967          |Master       |Together          |62513     |0          |1           |2013-09-09     |16         |520         |42           |98                 |0                  |42                  |14              |2                    |6                  |4                      |10                   |6                    |0               |0               |0               |0               |0               |0           |7446  |0           |3                |11           |\n",
      "|1971          |Graduation   |Divorced          |55635     |0          |1           |2012-11-13     |34         |235         |65           |164                |50                 |49                  |27              |4                    |7                  |3                      |7                    |6                    |0               |0               |0               |0               |0               |0           |965   |0           |3                |11           |\n",
      "|1985          |PhD          |Married           |33454     |1          |0           |2013-05-08     |32         |76          |10           |56                 |3                  |1                   |23              |2                    |4                  |0                      |4                    |8                    |0               |0               |0               |0               |0               |0           |6177  |0           |3                |11           |\n",
      "|1950          |PhD          |Together          |5648      |1          |1           |2014-03-13     |68         |28          |0            |6                  |1                  |1                   |13              |1                    |1                  |0                      |0                    |20                   |0               |0               |1               |0               |0               |0           |5899  |0           |3                |11           |\n",
      "|1983          |Graduation   |Married           |NULL      |1          |0           |2013-11-15     |11         |5           |5            |6                  |0                  |2                   |1               |1                    |1                  |0                      |2                    |7                    |0               |0               |0               |0               |0               |0           |1994  |0           |3                |11           |\n",
      "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(\"No of rows in MARKETING_ADDITIONAL table: \",marketing_additional.count())\n",
    "print(\"No of rows in PURCHASE_HISTORY table: \",final_combined.count())\n",
    "\n",
    "final_appended = final_combined.union_by_name(marketing_additional)\n",
    "\n",
    "print(\"No of rows in UPDATED table: \",final_appended.count())\n",
    "final_appended.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_appended.write.save_as_table(\"MARKETING_FINAL\", mode=\"overwrite\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
